﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;

namespace Manager
{
    class SQL
    {
        #region properties
        string strConnect = ConfigurationManager.ConnectionStrings["Manager.Properties.Settings.QuanLyConnectionString"].ConnectionString;
        SqlConnection conn;
        SqlCommand cmd;
        SqlDataAdapter adapter;
        #endregion

        public SQL() 
        {
            conn = new SqlConnection(strConnect);
        }

        public SqlConnection open() 
        {
            try
            {
                conn.Open();
                return conn;
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error..", ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }              
        }

        public void close() 
        {
            try
            {
                conn.Close();
            }
            catch (SqlException ex) 
            {
                MessageBox.Show("Error...", ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }       
        public void query_deleteData(string table, string key, string value)
        {
            open();
            cmd = new SqlCommand(table, conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue(key, value);
            cmd.ExecuteNonQuery();
            close();
            MessageBox.Show("delete successful");
        }       

        #region   select sql...
        public DataTable getDataTable(string strStoredProcedures) 
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                this.close();
            }
        }
        public DataTable getDataTable(string strStoredProcedures,SqlParameter sqlPara)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                cmd.Parameters.Add(sqlPara);

                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                this.close();
            }
        }

        public DataTable getDataTable(string strStoredProcedures, SqlParameter[] sqlParameters) 
        {
            try 
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                foreach (SqlParameter sqlPara in sqlParameters) 
                {
                    cmd.Parameters.Add(sqlPara);
                }
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                this.close();
            }
        }
        #endregion

        #region execute insert, update, delete

        public void execute_Query(string strStoredProcedures)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                cmd.ExecuteNonQuery();
                MessageBox.Show("Execute successful..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);               
            }
            finally
            {
                this.close();
            }
        }

        public void execute_Query(string strStoredProcedures, SqlParameter sqlPara)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();

                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                cmd.Parameters.Add(sqlPara);
                cmd.ExecuteNonQuery();
                
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);               
            }
            finally
            {
                this.close();
            }
        }

        public void execute_Query(string strStoredProcedures, SqlParameter[] sqlParameters)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                foreach (SqlParameter sqlPara in sqlParameters)
                {
                    cmd.Parameters.Add(sqlPara);
                }
                cmd.ExecuteNonQuery();
                
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);                
            }
            finally
            {
                this.close();
            }
        }
        #endregion

        public DataSet sql_report(string report) 
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = report;

                cmd.ExecuteNonQuery();
                DataSet ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds,"Salary");
                return ds;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                this.close();
            }

        }
    }
}
